CREATE PROC [dbo].[STIGetInfoAcc] @FactorId DECIMAL(20, 0)
AS  
Begin
    ----------------------------------------- Declare RKara variables
    DECLARE @InvoiceKind SMALLINT = 0;
    DECLARE @Stamp BIGINT;
    DECLARE @TotalReceivedAmount DECIMAL(20, 0) = 0;
    DECLARE @SalesInvoiceId DECIMAL(20, 0) = 0;
    DECLARE @SalesInvoiceNo VARCHAR(20) = 0;
    DECLARE @ReturnInvoiceId DECIMAL(20, 0);
    DECLARE @ReturnInvoiceNo DECIMAL(20, 0);
    DECLARE @GuyCode VARCHAR(5);
    DECLARE @EconomicCode VARCHAR(MAX);
    DECLARE @CompanyEconomicCode VARCHAR(MAX);
    DECLARE @NetTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @ReduceTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @AfterReduceTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @TaxTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @InvoiceTotalAmount DECIMAL(20, 0);
    DECLARE @ReceiptAmount DECIMAL(20, 0) = 0;
    DECLARE @CashAndChequeAmount DECIMAL(20, 0) = 0;
    DECLARE @DecimalZero DECIMAL(1, 0) = 0;
    DECLARE @BuyerPostalCode VARCHAR(10);
    DECLARE @TypeOfBuyer TINYINT;
    DECLARE @ReferenceTaxId VARCHAR(MAX) = NULL;
    --        
    ----------------  Get Company Economic Code

    SELECT TOP (1)
           @CompanyEconomicCode = ISNULL(c.CodeEghtesadi, NULL)
    FROM Manager.dbo.Cooprations c
    WHERE AutoId =
    (
        SELECT AutoId FROM Manager.dbo.Years WHERE NameYear = DB_NAME()
    );


    ---------------- Declaer Result Table
    DECLARE @Detail TABLE
    (
        Kind SMALLINT NOT NULL
            DEFAULT (0),
        GoodsCode VARCHAR(15) NOT NULL,
        DeliverCount DEC(20, 3) NOT NULL,
        SalesAmount MONEY NOT NULL,
        ReducedAmounts DEC(20, 0) NOT NULL,
        HasTax BIT NOT NULL
            DEFAULT (0),
        TaxPrice DECIMAL(20, 0) NOT NULL
            DEFAULT (0),
        TaxPercent DEC(2, 0) NOT NULL
            DEFAULT (0)
    );

    ----------------
    --    
    --    PrescriptionKind =>    ѐ    1 ѐ 2   3

    ------------------------ Detect Prescription Type
    SELECT @InvoiceKind = CASE
                              WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                  1
                              WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                  2
                          END,
           @SalesInvoiceNo = CASE
                                 WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                     H.Nu_Forosh
                                 ELSE
                                     0
                             END,
           @ReturnInvoiceNo = CASE
                                  WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                      H.Nu_Bargasht_Forosh
                                  ELSE
                                      0
                              END,
           @SalesInvoiceId = CASE
                                 WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                     H.Id_Havaleh
                                 ELSE
                                     0
                             END,
           @ReturnInvoiceId = CASE
                                  WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                      H.Id_Havaleh
                                  ELSE
                                      0
                              END,
           @Stamp = REPLACE(dbo.MiladiToShamsi(H.Tarikh_faktor), '/', '') + REPLICATE('0', 4)
    --dbo.MiladiToShamsi(H.Tarikh_faktor)
    --dbo.UNIX_TIMESTAMP(H.Tarikh_faktor)
    FROM dbo.headH H WITH (NOLOCK)
    WHERE H.Id_Havaleh = @FactorId;

    ----------------------------------------  Print Values 

    --SELECT @InvoiceKind PrescriptionKind,
    --       @SalesInvoiceNo SalesInvoiceNo,
    --       @ReturnInvoiceNo ReturnInvoiceNo,
    --       @SalesInvoiceId SalesInvoiceId,
    --       @ReturnInvoiceId ReturnInvoiceId;

    ------------------------- Detect Reference Invoice
    IF @InvoiceKind = 2
    BEGIN
        SELECT @SalesInvoiceId = H.Id_Havaleh
        FROM dbo.headH H WITH (NOLOCK)
        WHERE H.Kind_Flag IN ( 5, 7, 9 )
              AND H.Nu_Forosh IN
                  (
                      SELECT TOP (1)
                             Nu_Forosh
                      FROM dbo.headH WITH (NOLOCK)
                      WHERE Id_Havaleh = @FactorId
                            AND Kind_Flag IN ( 0, 6, 8 )
                      ORDER BY Tarikh DESC
                  );
        ----------------------------- Insert Detail 
        SET NOCOUNT ON;
        INSERT INTO @Detail -- Reference 
        SELECT 1,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               -- ISNULL(SUM(R.TaxPrice), 0),
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0)
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G WITH (NOLOCK)
                ON G.K_Code = D.K_Code
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @SalesInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId;
        -----------------------------------------
        INSERT INTO @Detail --@ReturnDetail
        SELECT 2,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               --ISNULL(SUM(R.TaxPrice), 0),
               ISNULL(SUM(R.TaxPercent), 0)
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G
                ON G.K_Code = D.K_Code
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @ReturnInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId;
        ---------------- Insert Not Return
        INSERT INTO @Detail --@ResultDetail 
        SELECT 3,
               resultTable.GoodsCode,
               SUM(resultTable.DeliverCount),
               SUM(resultTable.SalesAmount),
               SUM(resultTable.ReducedAmounts),
               resultTable.HasTax,
               SUM(resultTable.TaxPrice),
               SUM(resultTable.TaxPercent)
        FROM
        (
            SELECT GoodsCode,
                   DeliverCount,
                   SalesAmount,
                   ReducedAmounts,
                   HasTax,
                   TaxPrice,
                   TaxPercent
            FROM @Detail
            WHERE Kind = 1
            UNION
            SELECT GoodsCode,
                   -1 * DeliverCount,
                   -1 * SalesAmount,
                   -1 * ReducedAmounts,
                   HasTax,
                   -1 * TaxPrice,
                   TaxPercent
            FROM @Detail
            WHERE Kind = 2
        ) AS resultTable
        GROUP BY resultTable.GoodsCode,
                 resultTable.HasTax;

        DELETE FROM @Detail
        WHERE DeliverCount <= 0;
        ----------------------- Detect Invoice is Cancelled ???
        IF NOT EXISTS (SELECT 1 FROM @Detail WHERE Kind = 3)
            SET @InvoiceKind = 3;
        ----------------------------------------- Set The Recipient of the invoice
        SELECT TOP (1)
               @GuyCode = D.Sender
        FROM dbo.Havaleh D
        WHERE Id_Havaleh = @ReturnInvoiceId;
    ------------------------- End Of Return Invoice
    END;

    IF @InvoiceKind = 1
       OR @InvoiceKind = 3
    BEGIN

        --------------------------- Prepare Detail 
        SET NOCOUNT ON;
        INSERT INTO @Detail -- Result 
        (
            Kind,
            GoodsCode,
            DeliverCount,
            SalesAmount,
            ReducedAmounts,
            HasTax,
            TaxPrice,
            TaxPercent
        )
        SELECT 3,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               --ISNULL(SUM(R.TaxPrice), 0),
               ISNULL(SUM(R.TaxPercent), 0)
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G --WITH (NOLOCK)
                ON G.K_Code = D.K_Code
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @SalesInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId;


    END;
    
     -------------------------- Find TaxId

    IF @InvoiceKind = 2
       OR @InvoiceKind = 3
    BEGIN
        SELECT @ReferenceTaxId = TaxId
        FROM dbo.STITransaction
        WHERE InvoiceId = @SalesInvoiceId
              AND [Type] = 5
              AND [Status] = 1;
    END;

    ----------------------- Calc POS And Cash Receipt Amount

    SELECT @ReceiptAmount = SUM(R.Price)
    FROM dbo.Over_Under_Factor R
        JOIN dbo.Over_Under RD
            ON RD.AutoId = R.Code_Over_Under
               AND ISNULL(RD.TaxTransactionType, 0) = 1;
    ------------------------ Calc Header Total Amount

    SELECT @NetTotalAmount = SUM(FLOOR(D.SalesAmount)),
           @ReduceTotalAmount = FLOOR(SUM(D.ReducedAmounts)),
           @AfterReduceTotalAmount = SUM(FLOOR(D.SalesAmount - D.ReducedAmounts)),
           @TaxTotalAmount = FLOOR(SUM(D.TaxPrice)),
           @InvoiceTotalAmount = SUM(FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice))
    --FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice) tsstam,

    FROM @Detail D
    WHERE Kind = 3;
    --SELECT * FROM @Detail D
    --   WHERE Kind = 3; 

    ----------------------------------------- Set The Recipient of the invoice
    SELECT TOP (1)
           @GuyCode = D.Reciver
    FROM dbo.Havaleh D
    WHERE Id_Havaleh = @SalesInvoiceId;

    --------------------
    --   SELECT *
    --   FROM @Detail
    --WHERE Kind = 3;

    --------------------- Get Econmic Code of Person

    SELECT @BuyerPostalCode = ISNULL(G.Postal_Code, ''),
           @TypeOfBuyer = CASE
                              WHEN ISNULL(I.HcForoshandeTypeCode, 1) = 1 THEN
                                  1
                              ELSE
                                  I.HcForoshandeTypeCode
                          END,
           @EconomicCode = CASE
                               WHEN Tax_Code IS NULL
                                    OR Tax_Code = '' THEN
                                   NULL
                               ELSE
                                   Tax_Code
                           END
    FROM dbo.Guy G
        LEFT JOIN dbo.tblGuy_Info I
            ON I.Guy_Code = G.Guy_Code
    WHERE G.Guy_Code = @GuyCode;


    ----------------- Calc Cash and Check Amount
    SELECT @CashAndChequeAmount = SUM(Ch_Price)
    FROM dbo.Cheqe
    WHERE Id_Cheqe =
    (
        SELECT TOP (1)
               H.Id_Cheqe
        FROM dbo.HeadC H
        WHERE Id_Havaleh = @FactorId
        ORDER BY H.Id_Cheqe
    );

    -------------------------------------------Print Value
    --SELECT @NetTotalAmount NetTotalAmount,
    --       @ReduceTotalAmount ReduceTotalAmount,
    --       @AfterReduceTotalAmount AfterReduceTotalAmount,
    --       @TaxTotalAmount TaxTotalAmount,
    --       @InvoiceTotalAmount InvoiceTotalAmount,
    -- @ReceiptAmount ReceiptAmount, 
    --       @CashAndChequeAmount CashAndChequeAmount;
    --RETURN;

    --------------------  Select Inoive Header --------------------

    SELECT @AfterReduceTotalAmount tadis,
           @ReduceTotalAmount tdis,
           @NetTotalAmount tprdis,
           @TaxTotalAmount tvam,
           @InvoiceTotalAmount tbill,
           cap = CASE
                     WHEN (ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) - ISNULL(@ReduceTotalAmount, 0)) < 0 THEN
                         0
                     ELSE
           (ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) - ISNULL(@ReduceTotalAmount, 0))
                 END,
                     --ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) - ISNULL(@ReduceTotalAmount, 0) cap,
           insp = CASE
                      WHEN (@InvoiceTotalAmount
                            - ((ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0)
                                + ISNULL(@TaxTotalAmount, 0)
                               )
                              )
                           ) < 0 THEN
                          0
                      ELSE
           (@InvoiceTotalAmount
            - ((ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0) + ISNULL(@TaxTotalAmount, 0)))
           )
                  END,
           '' taxid,
           NULL bbc, --EHS
           NULL bid,
           '' billid,
           NULL bpc,
                     --'' bpn, EHS
           '' crn,
           0 dpvb,
                     --0 ft, EHS
           @Stamp indatim,
           @Stamp indati2m,
           inno = DB_NAME() + CAST(CASE
                                       WHEN @InvoiceKind in  (2,3) THEN
                                           @ReturnInvoiceNo
                                       ELSE
                                           @SalesInvoiceNo
                                   END AS VARCHAR(MAX)),
           irinno = CASE
                        WHEN @InvoiceKind = 1 THEN
                            NULL
                        ELSE
                            DB_NAME() + @SalesInvoiceNo
                    END,
           1 inp,
           ins = CASE
                     WHEN @InvoiceKind = 1 THEN
                         1
                     WHEN @InvoiceKind = 2 THEN
                         4
                     WHEN @InvoiceKind = 3 THEN
                         3
                 END,
           CASE
               WHEN @EconomicCode = NULL
                    OR @EconomicCode IS NULL THEN
                   2
               ELSE
                   1
           END inty,
           @ReferenceTaxId irtaxid,
           NULL sbc, --ESH
           NULL scc, --ESH
           '' scln,
           setm = CASE
                      WHEN ((@CashAndChequeAmount + @ReceiptAmount) > 0)
                           AND @TotalReceivedAmount > 0 THEN
                          3
                      WHEN @TotalReceivedAmount = 0 THEN
                          2
                      ELSE
                          1
                  END,
           @DecimalZero tax17,
           @EconomicCode tinb,
           @CompanyEconomicCode tins,
           @TypeOfBuyer tob,
           @DecimalZero todam,
           @DecimalZero tvop;

    --------------------SELECT DETAILS--------------------------

    SELECT D.DeliverCount am,
           ROUND(CAST((D.SalesAmount / D.DeliverCount) AS DEC(20, 2)), 2) fee,
                                                                           --FLOOR(CAST(D.SalesAmount AS DEC(20, 0))) prdis,
           CAST(FLOOR(D.SalesAmount) AS DEC(20, 0)) prdis,
           D.ReducedAmounts dis,
           CAST(FLOOR(D.SalesAmount - D.ReducedAmounts) AS DEC(20, 0)) adis,
           D.TaxPercent vra,
           D.TaxPrice vam,
           CAST(FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice) AS DEC(20, 0)) tsstam,
           @DecimalZero bros,
           '' bsrn,
           ROUND(CAST((D.SalesAmount / D.DeliverCount) AS MONEY), 2) cfee, --EHS
           @DecimalZero consfee,
           @DecimalZero cop,
           NULL cut,                                                       --EHS
           NULL exr,                                                       --EHS
           CAST(ISNULL(UM.TaxCode, '1627') AS VARCHAR(10)) mu,
           @DecimalZero odam,
           @DecimalZero odr,
           '0' odt,
           @DecimalZero olam,
           @DecimalZero olr,
           '0' olt,
           @DecimalZero spro,
           ISNULL(
                     (ISNULL(GM.TaxCode, STUFF(REPLICATE('0', 13), 14 - LEN(G.K_Code), LEN(G.K_Code), G.K_Code))),
                     SUBSTRING(G.K_Code, 1, 13)
                 ) sstid,
                                                                           --'2720000000050' sstid,
           CASE
               WHEN ISNULL(GM.TaxName, '') = '' THEN
                   G.Name1
               ELSE
                   GM.TaxName
           END AS sstt,
           @DecimalZero tcpbs,
           @DecimalZero vop
    FROM @Detail D -- Result
        JOIN dbo.KalaId G WITH (NOLOCK)
            ON G.K_Code = D.GoodsCode
        LEFT JOIN dbo.STIGoodsMapping GM
            ON GM.GoodsCode = G.K_Code
        LEFT JOIN dbo.STIUnitMaping UM
            ON UM.UnitCode = G.U_Code1
    WHERE D.Kind = 3;

    ---------------------------------------------PAYMENT
    SELECT '' acn,  --EHS
           '' iinn, --EHS
           '' pcn,  --EHS
           (CAST(NULL AS BIGINT)) pdt,
           '' pid,  --EHS
           '' trmn, --EHS
           '' trn;  --EHS
end
